大家好！我是只谈技术不剪发的 Tony 老师。

除了标准的 SQL 模式匹配（LIKE 运算符）功能之外，MySQL 还提供了基于扩展正则表达式的模式匹配功能，类似于 Unix 操作系统中的 vi、grep 以及 sed 工具。本文给大家详细介绍一下 MySQL 中正则表达式函数和运算符的语法和使用。

如果你觉得文章有用，欢迎评论📝、点赞👍、推荐🎁

SQL 模式匹配使用 LIKE 和 NOT LIKE 运算符表示，使用下划线（_）匹配任意单个字符，使用百分号（%）匹配任意多个字符。MySQL 默认不区分字符的大小写。

正则表达式是实现复杂搜索的一种强大方法，一个正则表达式描述了一组字符串。最简单的正则表达式就是一个普通字符串，例如正则表达式“hello”只匹配字符串“hello”。

正则表达式通常包含一些特殊的结构，用于匹配多个不同的字符串。例如，正则表达式“hello|world”包含了替换操作符（|），可以匹配字符串“hello”或者字符串“world”。

“B[an]*s”是一个更复杂的正则表达式，它可以匹配字符串“Bananas”、“Baaaaas”、“Bs”以及任何以字母 B 开始、以字母 s 结束并且包含零个或者多个字母 a 或字母 n 的字符串。

下面我们就来介绍一些正则表达式中具有特殊意义的字符和结构，REGEXP_LIKE 是一个正则表达式函数，具体介绍参考下文。

字符 ^ 匹配字符串的开始，例如：

字符 $ 匹配字符串的结束，例如：

字符 . 匹配任意字符（包括回车和换行。如果想要匹配字符串内部的回车和换行，需要使用多行匹配，可以指定匹配控制符 m 或者在模式修饰符中指定 ?m）。例如：

a* 匹配零个或者多个字符串 a，例如：

a+ 匹配一个或者多个字符 a，例如：

a? 匹配零个或者一个字符 a，例如：

de|abc 表示二选一，匹配字符串 de 或者 abc。例如：

(abc)* 匹配零个或者多个字符串 abc，例如：

{1}, {2,3} 表示匹配指定次数。{n} 和 {m,n} 提供一种更通用的编写正则表达式的方法，用于多次匹配前一个元素（或者一组元素）。m 和 n 都是整数。

进一步来说，a{n} 表示字符 a 出现了 n 次；a{n,} 表示字符 a 出现了 n 次以上；a{m,n} 表示字符 a 出现了 m 到 n 次，m 必须小于 n。

[a-dX] 匹配字符 a、b、c、d 或者 X，[^a-dX] 匹配非 a、b、c、d 或者 X 的其他字符。- 表示匹配两个字符之间的所有字符，例如 [0-9] 表示匹配任一数字。如果想要包含字符 ] 自身，它必须写在 [ 的后面；如果想要包含字符 - 自身，它必须是括号内的第一个或者最后一个字符。其他任何没有特殊意义的字符只匹配它自己。例如：

[=character_class=]，当 [=character_class=] 出现在另一个方括号内时表示一个等价字符类。它可以匹配拥有相同排序规则值的所有字符。例如，如果 o 和 (+) 是一个等价类，[[=o=]]、[[=(+)=]] 和 [o(+)] 作用相同。等价类不能作为范围匹配的开始或者结束。

[:character_class:]，当 [:character_class:] 出现在另一个方括号内时表示一个字符类，它可以匹配该字符类种的所有字符。下表列出了标准的字符类名称，特定的本地化设置可能还会提供其他的字符类。字符类不能作为范围匹配的开始或者结束。

例如：

如果想要在正则表达式种匹配特殊字符，可以在该字符前增加两个反斜线（\）。MySQL 解析器会解释其中一个反斜线，正则表达式代码库会解释另一个反斜线。例如，如果想要匹配字符串 1+2 ，其中字符 + 是一个特殊字符，下面只有最后一个语句能够正确匹配：

REGEXP_LIKE(expr, pat[, match_type]) 函数当字符串 expr 匹配正则表达式模式 pat 时返回 1，否则返回 0 。如果 expr 或者 pat 为 NULL，函数返回 NULL。

关于正则表达式模式的语法可以参考上一节内容，模式不一定是字符串常量。例如，模式可以是一个字符串表达式或者表中的字段。

可选的 match_type 参数指定了执行模式匹配的一些行为，可以是以下字符的组合：

如果 match_type 选项中指定了互相矛盾的方式，将会使用最后指定的方式。

默认情况下，正则表达式运算使用 expr 和 pat 的字符集和排序规则决定字符的类型和比较结果。如果这两个参数的字符集和排序规则不同，将会使用 Section 10.8.4, “Collation Coercibility in Expressions” 中指定的规则进行转换。通过在参数中显式指定排序规则，可以修改默认的比较结果：

match_type 可以通过指定 c 或者 i 决定是否区分大小写。但是有一个例外：如果任何参数是二进制字符串，使用区分大小写的方式进行匹配，即使 match_type 包含了字符 i。

注意：MySQL 使用 C 语言转义语法（例如，\n 代表了换行符）。如果想要在 expr 或者 pat 参数中使用字面值 \，必须写两次。除非设置了 SQL 模式 NO_BACKSLASH_ESCAPES，表示不使用转义字符。

REGEXP 运算符是 REGEXP_LIKE() 函数的同义词，使用默认的 match_type 参数。例如：

另外，expr NOT REGEXP pat表示NOT (expr REGEXP pat)，也就是不匹配某个正则表达式。

RLIKE 运算符也是 REGEXP_LIKE() 函数的同义词，使用默认的 match_type 参数。

同样，expr NOT RLIKEpat等价于NOT (expr REGEXP pat)，也就是不匹配某个正则表达式。

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]]) 函数返回字符串 expr 中匹配模式 pat 的子串的位置，如果没有找到匹配的子串则返回 0。如果 expr 或者 pat 为 NULL，函数返回 NULL。字符的位置从 1 开始。

REGEXP_INSTR() 函数包含以下可选参数：

例如：

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]]) 函数返回字符串 expr 中匹配模式 pat 的子串，如果没有找到匹配的子串则返回 NULL。如果 expr 或者 pat 为 NULL，函数返回 NULL。

REGEXP_SUBSTR() 函数支持以下可选的参数：

例如：

MySQL 8.0.17 版本之前该函数返回的字符集为 UTF-16 ，MySQL 8.0.17 版本开始返回的字符集和排序规则与被搜索的字符串相同。

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]]) 函数将字符串 expr 中匹配模式 pat 的子串替换为 repl 并返回替换后的结果。如果 expr、pat 或者 repl 为 NULL，函数返回 NULL。

REGEXP_REPLACE() 函数支持以下可选参数：

例如：

MySQL 8.0.17 版本之前该函数返回的字符集为 UTF-16 ，MySQL 8.0.17 版本开始返回的字符集和排序规则与被搜索的字符串相同。

REGEXP_LIKE() 以及相关函数使用的系统资源可以通过以下变量进行控制：

MySQL 8.0.4 之前版本使用 Henry Spencer 正则表达式代码库实现正则表达式操作，而不是现在的 Unicode 国际组件（ICU）。这两者之间的一些差异可能会影响应用程序：

对于 Henry Spencer 代码库，REGEXP 和 RLIKE 操作符以字节为单位进行查找，因此它们不具有多字节安全性，可能会对多字节字符集的匹配产生错误。另外，这些操作符使用字节值进行比较，重音字符可能会被认为与相应非重音字符不相等，即使排序规则中认为它们相等。

ICU 完全支持 Unicode，并且具有多字节安全性。正则表达式函数将所有的字符串看作 UTF-16 字符串。需要注意的是，所有的下标位置都是基于 16 比特段，而不是代码点（字符编码值）。这就意味着长度超过一个 16 比特段的字符可能会返回意料之外的结果，例如：

Unicode 基本多语言平面之内的字符（包含了大多数现代语言中的字符）在这方面是安全的：

表情符号，例如前面两个示例中的寿司符号 🍣（U+1F363），不属于基本多语言平面，而属于 Unicode 补充多语言平面。REGEXP_SUBSTR() 或相关函数处理表情符号或者其他 4 字节字符时，如果从某个字符中间开始查找也会产生问题。以下两个示例分别从第一个参数的第二个 2字节位置开始查找，第一个示例对于 2 字节（BMP）字符能够正常返回结果，第二个示例对于包含 4 字节字符的数据返回了错误的结果，因为第一个 2 字节被拆分了，从而导致后面的字符数据未能正确解析。

对于 . 符号，Spencer 代码库可以匹配任何位置（包括行内）的行终止符（回车、换行）。对于 ICU，需要指定 m 控制符号才能匹配行内的行终止符。

Spencer 代码库支持词首和词尾边界标记（[[::]] ），ICU 不支持这种语法。对于 ICU，可以使用 \b 匹配单词边界；需要使用两个反斜线，因为 MySQL 会解释其中一个转移符号。

Spencer 代码库支持 [.characters.] 语法，ICU 不支持这种语法。

对于重复次数（{n} 和 {m,n}）Spencer 代码库支持的最大值为 255。ICU 没有这个限制，虽然系统变量 regexp_time_limit 可以限制匹配引擎的最大步骤。

ICU 将圆括号当作元字符处理。为了在正则表达式中使用它们的字面值，需要进行转义：

ICU 同样也会将方括号作为元字符处理，但是只有开口方括号（[）需要转义才能作为常量字符使用：

MySQL 8.0.22 之前版本中的正则表达式函数支持二进制字符串参数，但是返回的结果可能不一致。MySQL 8.0.22 以及之后的版本如果使用二进制字符串作为参数，将会返回 ER_CHARACTER_SET_MISMATCH 错误。